package com.hvgroup.modules.sanic.service.impl;

import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.hvgroup.modules.sanic.service.FileChatService;
import com.hvgroup.modules.sanic.util.MinioUtil;
import com.hvgroup.utils.StringUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.MediaType;
import org.springframework.http.codec.ServerSentEvent;
import org.springframework.stereotype.Service;
import org.springframework.web.reactive.function.BodyInserters;
import org.springframework.web.reactive.function.client.WebClient;
import reactor.core.publisher.Flux;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.*;
import java.time.Duration;
import java.util.*;
import java.util.stream.Collectors;


@Slf4j
@Service
@RequiredArgsConstructor
public class FileChatServiceImpl implements FileChatService {

    private final ObjectMapper objectMapper = new ObjectMapper();
    private final MinioUtil minioUtil;

    @Value("${dify.api.key.app}")
    private String difyApiKey;

    @Value("${dify.api.url.root}")
    private String difyApiUrl;

    //--------------------- csv read ------------------------

    @Override
    public String readExcel(String fileUrl) {
        try {
            String extension = FilenameUtils.getExtension(fileUrl).split("\\?")[0].toLowerCase();
            InputStream inputStream = new URL(fileUrl).openStream();

            ObjectNode sheetResult = objectMapper.createObjectNode();

            if (extension.equals("xlsx") || extension.equals("xls")) {
                Workbook workbook = WorkbookFactory.create(inputStream);
                for (Sheet sheet : workbook) {
                    ObjectNode sheetData = objectMapper.createObjectNode();

                    Iterator<Row> rowIterator = sheet.rowIterator();
                    ArrayNode header = objectMapper.createArrayNode();
                    ArrayNode data = objectMapper.createArrayNode();

                    if (rowIterator.hasNext()) {
                        Row firstRow = rowIterator.next();
                        firstRow.forEach(cell -> header.add(cell.toString()));
                    }

                    if (rowIterator.hasNext()) {
                        Row secondRow = rowIterator.next();
                        ArrayNode rowData = objectMapper.createArrayNode();
                        secondRow.forEach(cell -> rowData.add(cell.toString()));
                        data.add(rowData);
                    }

                    sheetData.set("excel表头", header);
                    sheetData.set("excel数据", data);
                    sheetResult.set(sheet.getSheetName(), sheetData);
                }
                workbook.close();
            } else if (extension.equals("csv")) {
                BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
                CSVParser parser = CSVFormat.DEFAULT.parse(reader);
                List<CSVRecord> records = parser.getRecords();

                ArrayNode header = objectMapper.createArrayNode();
                ArrayNode data = objectMapper.createArrayNode();

                if (!records.isEmpty()) {
                    CSVRecord firstRow = records.get(0);
                    for (String val : firstRow) header.add(val);
                }

                if (records.size() > 1) {
                    CSVRecord secondRow = records.get(1);
                    ArrayNode rowData = objectMapper.createArrayNode();
                    for (String val : secondRow) rowData.add(val);
                    data.add(rowData);
                }

                ObjectNode sheetData = objectMapper.createObjectNode();
                sheetData.set("excel表头", header);
                sheetData.set("excel数据", data);
                sheetResult.set("sheet1", sheetData);

            } else {
                throw new IllegalArgumentException("Unsupported file extension: " + extension);
            }

            return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(sheetResult);

        } catch (Exception e) {
            log.error("读取 Excel/CSV 文件失败", e);
            throw new RuntimeException("读取 Excel/CSV 文件失败", e);
        }
    }


    @Override
    public String readFileColumns(String fileUrl) {
        try {
            String extension = FilenameUtils.getExtension(fileUrl).split("\\?")[0].toLowerCase();
            InputStream inputStream = new URL(fileUrl).openStream();

            ArrayNode columns = objectMapper.createArrayNode();

            if (extension.equals("xlsx") || extension.equals("xls")) {
                Workbook workbook = WorkbookFactory.create(inputStream);
                Sheet sheet = workbook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                if (rowIterator.hasNext()) {
                    Row headerRow = rowIterator.next();
                    headerRow.forEach(cell -> columns.add(cell.toString()));
                }
                workbook.close();
            } else if (extension.equals("csv")) {
                BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
                CSVParser parser = CSVFormat.DEFAULT.parse(reader);
                List<CSVRecord> records = parser.getRecords();
                if (!records.isEmpty()) {
                    for (String col : records.get(0)) {
                        columns.add(col);
                    }
                }
            } else {
                throw new IllegalArgumentException("Unsupported file extension: " + extension);
            }

            return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(columns);
        } catch (Exception e) {
            log.error("读取列名失败", e);
            return "{\"error\": \"" + e.getMessage() + "\"}";
        }
    }


    @Override
    public String queryExcel(String fileUrl, String queryText) {
        try {
            log.info("调用 Python 脚本进行智能查询: fileUrl={}, queryText={}", fileUrl, queryText);

            // 使用 python3 调用本地脚本，参数传入 fileUrl 和 queryText
            ProcessBuilder processBuilder = new ProcessBuilder(
                    "python3", "scripts/agent_query.py", fileUrl, queryText
            );
            processBuilder.redirectErrorStream(true); // 合并标准错误输出

            Process process = processBuilder.start();

            BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
            StringBuilder output = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                output.append(line);
            }

            int exitCode = process.waitFor();
            if (exitCode == 0) {
                return output.toString();
            } else {
                log.error("Python 脚本执行失败，退出码: {}，输出: {}", exitCode, output);
                return "{\"error\": \"Python 执行失败，退出码 " + exitCode + "\"}";
            }
        } catch (Exception e) {
            log.error("调用 Python 脚本失败", e);
            return "{\"error\": \"" + e.getMessage() + "\"}";
        }
    }


    // ... 之前 readExcel / readFileColumns 方法保留 ...
    /**
     * 文件问答处理：读取文件，运行 SQL 并返回列 + 结果
     */
    public  Map<String, Object>  processFileLlmQuery(String fileKey, String modelOutStr) {
        if (StringUtils.isEmpty(modelOutStr)) {
            log.error("文件问答大模型返回结果为空");
            throw new RuntimeException("模型输出为空");
        }

        try {
            String fileUrl = minioUtil.getPresignedUrl("filedata", fileKey);
            String extension = FilenameUtils.getExtension(fileKey).split("\\?")[0].toLowerCase();

            List<Map<String, Object>> rows;
            List<String> columns;

            try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1")) {
                Statement stmt = conn.createStatement();

                List<String> columnNames = new ArrayList<>();
                List<List<String>> dataRows = new ArrayList<>();

                if (extension.equals("xlsx") || extension.equals("xls")) {
                    List<Map<String, String>> excelData = readExcelFromUrl(fileUrl);
                    if (!excelData.isEmpty()) {
                        columnNames = new ArrayList<>(excelData.get(0).keySet());
                        for (Map<String, String> row : excelData) {
                            List<String> rowData = new ArrayList<>();
                            for (String col : columnNames) {
                                String value = row.getOrDefault(col, "").replaceAll("\\\\n|\n", "").trim();
                                rowData.add(value);
                            }
                            dataRows.add(rowData);
                        }
                    }
                } else if ("csv".equals(extension)) {
                    BufferedReader reader = new BufferedReader(new InputStreamReader(new URL(fileUrl).openStream()));
                    CSVParser parser = CSVFormat.DEFAULT.withFirstRecordAsHeader().parse(reader);
                    columnNames = new ArrayList<>(parser.getHeaderNames());
                    for (CSVRecord record : parser) {
                        List<String> row = new ArrayList<>();
                        for (String col : columnNames) {
                            row.add(record.get(col));
                        }
                        dataRows.add(row);
                    }
                } else {
                    throw new RuntimeException("不支持的文件扩展名: " + extension);
                }

                stmt.execute("DROP TABLE IF EXISTS excel_table");
                String createSql = "CREATE TABLE excel_table (" +
                        columnNames.stream()
                                .map(col -> "\"" + col.replaceAll("\\\\n|\n", "").replace("\"", "\"\"").trim() + "\" VARCHAR")
                                .collect(Collectors.joining(", "))
                        + ")";

                try {
                    log.info("建表SQL: {}", createSql);
                    stmt.execute(createSql);
                } catch (SQLException e) {
                    log.error("创建表失败: {}", createSql, e);
                    throw e;
                }

                String insertSql = "INSERT INTO excel_table (" +
                        columnNames.stream().map(col -> "\"" + col.replaceAll("\\\\n|\n", "").replace("\"", "\"\"").trim() + "\"")
                                .collect(Collectors.joining(", ")) +
                        ") VALUES (" + columnNames.stream().map(col -> "?").collect(Collectors.joining(", ")) + ")";

                try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
                    for (List<String> row : dataRows) {
                        for (int i = 0; i < row.size(); i++) {
                            ps.setString(i + 1, row.get(i));
                        }
                        ps.addBatch();
                    }
                    ps.executeBatch();
                }
                JSON.toJSONString(dataRows);
                JsonNode json = objectMapper.readTree(modelOutStr);
                String sql = json.get("sql").asText().replace("`", "").trim();
                if (sql.isEmpty()) {
                    throw new RuntimeException("SQL 为空");
                }

                for (String col : columnNames) {
                    if (col.contains("时间")) {
                        String updateSql = String.format("UPDATE excel_table SET \"%s\" = NULL WHERE TRIM(\"%s\") = ''", col, col);
                        stmt.execute(updateSql);
                        stmt.execute("ALTER TABLE excel_table ALTER COLUMN \"" + col + "\" SET DATA TYPE TIMESTAMP");
                    }
                }

                sql = sql.replaceAll("(?i)DATE\\((.*?)\\)", "CAST($1 AS DATE)")
                        .replaceAll("(?i)(\\w+)::timestamp", "CAST($1 AS TIMESTAMP)")
                        .replaceAll("(?i)DATE_TRUNC\\(\\s*'hour'\\s*,\\s*(.*?)\\)", "CAST($1 AS TIMESTAMP)")
                        .replaceAll("(?i)DATE_TRUNC\\(\\s*'day'\\s*,\\s*(.*?)\\)", "CAST($1 AS DATE)")
                        .replaceAll("(?i)EXTRACT\\(HOUR FROM (.*?)\\)", "HOUR($1)")
                        .replaceAll("(?i)TO_CHAR\\(HOUR\\((.*?)\\), *'FM09'\\)", "LPAD(HOUR($1), 2, '0')")
                        .replaceAll("(?i)DATE_PART\\(\\s*'hour'\\s*,\\s*(.*?)::timestamp\\)", "HOUR(CAST($1 AS TIMESTAMP))")
                        .replaceAll("(?i)DATE_PART\\(\\s*'hour'\\s*,\\s*(.*?)\\)", "HOUR($1)")
                        .replaceAll("(?i)TIME_TRUNC\\(\\s*'hour'\\s*,\\s*(.*?)\\)", "CAST($1 AS TIMESTAMP)")
                        .replaceAll("(?i)TIME_BUCKET\\(\\s*(.*?),\\s*'1 hour'\\s*\\)", "CAST($1 AS TIMESTAMP)");

                ResultSet rs = stmt.executeQuery(sql);
                ResultSetMetaData meta = rs.getMetaData();
                columns = new ArrayList<>();
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    columns.add(meta.getColumnName(i));
                }

                rows = new ArrayList<>();
                while (rs.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (String col : columns) {
                        row.put(col, rs.getString(col));
                    }
                    rows.add(row);
                }
            }

            Map<String, Object> data = new LinkedHashMap<>();
            data.put("column", columns);
            data.put("result", rows);

            Map<String, Object> result = new LinkedHashMap<>();
            result.put("data", data);
            result.put("llm", JSON.parse(modelOutStr));
            return result;

        } catch (Exception e) {
            log.error("文件问答处理失败: {}, Error: {}", modelOutStr, e.getMessage(), e);
            throw new RuntimeException("文件问答执行失败", e);
        }
    }

    public List<Map<String, String>> readExcelFromUrl(String fileUrl) {
        List<Map<String, String>> result = new ArrayList<>();
        String extension = FilenameUtils.getExtension(fileUrl).split("\\?")[0].toLowerCase();
//        Workbook workbook = null;

        try (InputStream inputStream = new URL(fileUrl).openStream();
             Workbook workbook = "xlsx".equals(extension)
                     ? new XSSFWorkbook(inputStream)
                     : new HSSFWorkbook(inputStream)
        ) {

//            if (extension.equals(extension)) {
//                workbook = new XSSFWorkbook(inputStream);
//            } else if (extension.equals("xls")) {
//                workbook = new HSSFWorkbook(inputStream);
//            } else {
//                throw new IllegalArgumentException("不支持的文件类型: " + extension);
//            }

            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.rowIterator();

            List<String> headers = new ArrayList<>();
            if (rowIterator.hasNext()) {
                Row headerRow = rowIterator.next();
                for (Cell cell : headerRow) {
                    headers.add(cell.toString().trim());
                }
            }

            while (rowIterator.hasNext()) {
                Row dataRow = rowIterator.next();
                Map<String, String> rowMap = new LinkedHashMap<>();
                for (int i = 0; i < headers.size(); i++) {
                    Cell cell = dataRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    rowMap.put(headers.get(i), cell.toString().trim());
                }
                result.add(rowMap);
            }

        } catch (Exception e) {
            log.error("读取 Excel 文件失败", e);
            throw new RuntimeException("读取 Excel 文件失败: " + e.getMessage());
        }
//        finally {
//            try {
//                if (workbook != null) workbook.close();
//            } catch (IOException ignore) {
//            }
//        }

        return result;
    }


    /**
     * 使用 WebClient 流式调用 DiFy，并返回 SSE 流
     */
    public Flux<ServerSentEvent<String>> streamDifyAnswer(Map<String, Object> requestBody) {
        WebClient client = WebClient.builder()
                .baseUrl(difyApiUrl)
                .defaultHeader("Authorization", "Bearer " + difyApiKey)
                .defaultHeader("Content-Type", MediaType.APPLICATION_JSON_VALUE)
                .build();

        return client.post()
                .uri("/v1/chat-messages") // DiFy 的真实路径，如有不同请修改
                .contentType(MediaType.APPLICATION_JSON)
                .body(BodyInserters.fromValue(requestBody))
                .accept(MediaType.TEXT_EVENT_STREAM)
                .retrieve()
                .bodyToFlux(String.class)
                .map(data -> ServerSentEvent.builder(data).build())
                .timeout(Duration.ofSeconds(180))
                .doOnError(e -> log.error("DiFy SSE 失败", e));
    }

}
